/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controllers;

import Models.Cases;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author HUY
 */
public class CaseHistoryDAO {
    //methor load all rows in cases 

    public static List<Cases> loadAllCase() {
        List<Cases> list = new ArrayList<Cases>();
        Connection con = DBUtility.openConnection();
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = con.prepareCall("select * from Cases");
            rs = stm.executeQuery();
            while (rs.next()) {
                Cases cases = new Cases();
                cases.setCaseID(rs.getInt("CaseID"));
                cases.setCaseType(rs.getString("CaseType"));
                cases.setCaseTime(rs.getDate("CaseTime"));
                cases.setReportedTime(rs.getDate("ReportedTime"));
                cases.setApprovalDate(rs.getDate("ApprovalDate"));
                cases.setCasePlace(rs.getString("CasePlace"));
                cases.setCaseDescriptions(rs.getString("CaseDescriptions"));
                cases.setCaseInvolvements(rs.getString("CaseInvolvements"));
                cases.setCaseStatus(rs.getInt("CaseStatus"));
                cases.setProsecutionDesire(rs.getBoolean("ProsecutionDesire"));
                cases.setLeadOfficerID(rs.getInt("LeadOfficerID"));
                cases.setApprovalDirectorID(rs.getInt("ApprovalDirectorID"));
                list.add(cases);
            }

        } catch (SQLException ex) {
            Logger.getLogger(CaseHistoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtility.closeResultSet(rs);
            DBUtility.closeStatement(stm);
            DBUtility.closeConnection(con);
        }
        return list;
    }

    public static boolean addNewCases(Cases cases) {
        //SimpleDateFormat df = new SimpleDateFormat("dd/mm/yy");
        boolean result = false;
        Connection con = DBUtility.openConnection();
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = con.prepareStatement("insert Cases(CaseType,CaseTime,ReportedTime,ApprovalDate,CasePlace,CaseDescriptions,CaseInvolvements,CaseStatus,ProsecutionDesire,LeadOfficerID,ApprovalDirectorID) values  (?,?,?,?,?,?,?,?,?,?,?)");
            stm.setString(1, cases.getCaseType().trim());
            java.sql.Date _sqlCaseTime = new java.sql.Date(cases.getCaseTime().getTime());
            stm.setDate(2, _sqlCaseTime);
            java.sql.Date _sqlReportedTime = new java.sql.Date(cases.getReportedTime().getTime());
            stm.setDate(3, _sqlReportedTime);
            java.sql.Date _sqlApprovalDate = new java.sql.Date(cases.getApprovalDate().getTime());
            stm.setDate(4, _sqlApprovalDate);
            stm.setString(5, cases.getCasePlace());
            stm.setString(6, cases.getCaseDescriptions());
            stm.setString(7, cases.getCaseInvolvements());
            stm.setInt(8, cases.getCaseStatus());
            stm.setBoolean(9, cases.isProsecutionDesire());
            stm.setInt(10, cases.getLeadOfficerID());
            stm.setInt(11, 9);
            if (stm.executeUpdate() > 0) {
                result = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            DBUtility.closeResultSet(rs);
            DBUtility.closeStatement(stm);
            DBUtility.closeConnection(con);
        }
        
        return result;
    }

    public static boolean updateCases(Cases cases) {
        //SimpleDateFormat df = new SimpleDateFormat("dd/mm/yy");
        boolean result = false;
        Connection con = DBUtility.openConnection();
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = con.prepareStatement("update Cases set CaseType=?,CaseTime=?,ReportedTime=?,ApprovalDate=?,CasePlace=?,CaseInvolvements=?,"
                    + "CaseDescriptions=?,CaseStatus=?,ProsecutionDesire=?,LeadOfficerID=?,ApprovalDirectorID=?");
            stm.setString(1, cases.getCaseType().trim());
            stm.setString(2, cases.getCaseTime().toString());
            stm.setString(3, cases.getReportedTime().toString());
            stm.setString(4, cases.getApprovalDate().toString());
            stm.setString(5, cases.getCasePlace());
            stm.setString(6, cases.getCaseInvolvements());
            stm.setString(7, cases.getCaseDescriptions());
            stm.setInt(8, cases.getCaseStatus());
            stm.setBoolean(9, cases.isProsecutionDesire());
            stm.setInt(10, cases.getLeadOfficerID());
            stm.setInt(11, cases.getApprovalDirectorID());
            if (stm.executeUpdate() > 0) {
                result = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            DBUtility.closeResultSet(rs);
            DBUtility.closeStatement(stm);
            DBUtility.closeConnection(con);
        }

        return result;
    }
}
